<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Exceptionerror_m extends CI_Model {
	function __construct()
    {
        parent::__construct();
    }

	public function getnocostprice()
	{
		$dzgoods = 0;
		$dzgoodstwo = 0;
		$goodslist = $this->db->select('goods_id,goods_parent_sku,goods_sku,g_costprice,bulkprice')->get_where("db_goods"," g_costprice= 0 AND g_sale_status in(1,2,3)")->result_array();
		foreach($goodslist as $key=>$rs)
		{
			if($this->fun->getfactorygoodsku($rs["goods_sku"]))
			{
				$dzgoods = $dzgoods+1;
			}
			else{
				if(is_numeric($rs["goods_parent_sku"]) && preg_match('|^\d{8}$|',$rs["goods_parent_sku"]))	//数字并且8位SKU并且11 12开头
				{
					$dzgoodstwo = $dzgoodstwo+1;
				}
			}

		}
		
		$str="商品信息共".count($goodslist)."条没有填写成本价，其中工厂衣服 ".$dzgoods."条没有填写成本;关联商品共".$dzgoodstwo."条没有填写关联价";
		return $str;
	}

	public function gettwogoods()
	{
		$rsdb = $this->db->get_one("select `goods_sku`,count(*) as count from db_goods group by `goods_sku` having count>1");
		if($rsdb)
		{
			return  "一级错误：存在相同SKU信息->$rsdb[goods_sku]！请管理员检查漏洞！";
		}
	}

	public function sysworkbench($types=7)
	{

		$list = $this->db->order_by('id',"DESC")->limit(10)->get_where("db_sys_workbench",array('types'=>$types))->result_array();

		foreach($list as $key=>$rs)
		{
			 $goods_images = $this->db->get_where('db_goods_images', 'goods_id=' . $rs["number_id"])->result_array();
             $thumb_url = $goods_images[0]["thumb_url"];
			 $list[$key]['thumb_url'] = $thumb_url;
		}
		$rsdb = $this->db->get_one("select count(*) as count from db_sys_workbench where types =".$types."");
		return array("list"=>$list,"total"=>$rsdb['count']);
	}
	public function newmessage()
	{

		$list = $this->db->order_by('id',"DESC")->limit(10)->get_where("db_message",array('message_toID'=>$_COOKIE['userid']))->result_array();

		foreach($list as $key=>$rs)
		{
			$list[$key]['instime'] = date("Y-m-d H:i:s",$rs['instime']);
		}
		$rsdb = $this->db->get_one("select count(*) as count from db_message where message_toID ='".$_COOKIE['userid']."'");
		return array("list"=>$list,"total"=>$rsdb['count']);
	}
	
	public function getordercount($starttime,$endtime)
	{
//		$starttime =date("Y-m-01");	//开始时间
//    	$endtime   = date("Y-m-d");		//结束时间
//		
//		$starttime ='2017-09-01';	//开始时间
//    	$endtime   ='2017-09-13';		//结束时间
		/*查询绑定的店铺订单**/
		if($_COOKIE["department"] ==1)
		{
			
		}else{
			$shopname = $this->getshop();
			if($shopname)
			{
				$whereshop = $shopname;
			}
		}
    	//查询开始与结束时间每天的日收入
        $tpl_data=array('starttime'=>$starttime,'endtime'=>$endtime,'shopname'=>$shopname);
	
        if($whereshop)$wherestr=" AND shopname in('".$whereshop."')";
         $orders=$this->R->getOrderAmountList($starttime,$wherestr,$endtime,"dates",'dates');
		
            $daysales=array();
            foreach($orders as $key=>$val){
                $subtotal = $val['goods_amount_total'] +$val['shipping_fee_total'];
                $subzhic = $val['costprice_total']+$freight+$val['realexpress_fre']+ $val['payfee_total']+ $val['platform_fee_total'];
                $lirun = $subtotal - $subzhic;
        		if($subtotal){
        			$maoliv = round(($lirun/$subtotal)*100,2);
        		}else{
        			$maoliv=0;
        		}    
                $res=array("orderAmount"=>$val['goods_amount_total'],"shipping_fee"=>$val['shipping_fee_total'],"subtotal"=>round($subtotal),
                "cosprice"=>$val['costprice_total'],"freight"=>$val['realexpress_fre']
                ,"platformfee"=>$val['platform_fee_total'],"payfee"=>$val['payfee_total'],'orderDate'=>$val['dates'],
                "subzhic"=>number_format($subzhic,2),"goodsnNum"=>$val['sell_total'],"orderNum"=>$val['order_num'],"lirun"=>number_format($lirun,2),"maoliv"=>$maoliv."%");
                $daysales[]=$res;
                
          		$allorderAmount = $allorderAmount+$val['goods_amount_total'];	//合计
        		$allshipping_fee = $allshipping_fee+$val['shipping_fee_total'];
        		$allsubtotal = $allsubtotal+$subtotal;
        		$allcosprice = $allcosprice+$val['costprice_total'];
        		$allnum = $allnum+$val['sell_total'];
        		$allorderNum = $allorderNum+$val['order_num'];
        		$allfreight = $allfreight+$val['realexpress_fre'];
        		$allplatformfee = $allplatformfee+$val['platform_fee_total'];
        		$allpayfee = $allpayfee+$val['payfee_total'];
        		$allsubzhic = $allsubzhic+$subzhic;
        		$alllirun = $alllirun+$lirun;
            }
            $tpl_data['list']=$daysales;
            if($allsubtotal)
            		$allmaolv = round(($alllirun / $allsubtotal)*100,2);
            
			$tpl_data['alltotal']['allorderAmount'] = number_format($allorderAmount,2);
			$tpl_data['alltotal']['allshipping_fee'] = number_format($allshipping_fee,2);
			$tpl_data['alltotal']['allsubtotal'] = number_format($allsubtotal,2);
			$tpl_data['alltotal']['allcosprice'] = number_format($allcosprice,2);
			$tpl_data['alltotal']['allnum'] = number_format($allnum,2);
			$tpl_data['alltotal']['allorderNum'] = $allorderNum;
			$tpl_data['alltotal']['allfreight'] = number_format($allfreight,2);
			$tpl_data['alltotal']['allpayfee'] = number_format($allpayfee,2);
			$tpl_data['alltotal']['allsubzhic'] = number_format($allsubzhic,2);
			$tpl_data['alltotal']['alllirun'] = number_format($alllirun,2);
			$tpl_data['alltotal']['allmaolv'] = number_format($allmaolv,2);
			$tpl_data['alltotal']['allplatformfee'] = number_format($allplatformfee,2);

		return $tpl_data;
	}
	function newproduct()
	{
		$g_supplier="";
		if($_COOKIE["classid"] ==12)	//工厂1人员
		{
			$g_supplier = 1;
		}

		if($_COOKIE["classid"] ==13)	//工厂2人员
		{
			$g_supplier = 2;
		}
		if($_COOKIE["classid"] ==16)	//外部开发
		{
			$g_supplier = 7;
		}

		if($g_supplier==1 || $g_supplier==2){
			$sql = "SELECT * from db_goods where g_supplier = {$g_supplier} AND  length(goods_parent_sku)=8 AND (locate(11,goods_parent_sku)=1 or locate(12,goods_parent_sku)=1 ) and length(goods_sku)=8 and  goods_sku NOT REGEXP '-'  order by goods_id DESC LIMIT 0,8";
		}elseif($g_supplier==7){
			$sql = "SELECT * from db_goods where g_supplier = {$g_supplier} AND  goods_sku NOT REGEXP '-'  order by goods_id DESC LIMIT 0,8";
		}else{
			$sql = "SELECT * from db_goods where  length(goods_parent_sku)=8 AND (locate(11,goods_parent_sku)=1 or locate(12,goods_parent_sku)=1 ) and length(goods_sku)=8 and  goods_sku NOT REGEXP '-'  order by goods_id DESC LIMIT 0,8";
		}
		$query = $this->db->query($sql);
        $dates = $query->result_array();
		foreach($dates as $key=>$rs)
		{
			//获取推荐人员
			$arr_recommend = $this->db->get_where('db_product_develop', "sku='" . $rs["goods_parent_sku"]."'")->row_array();
			if(count($arr_recommend)>0){
				$arr_receivers = explode(",",trim($arr_recommend['receivers'],","));
				$arr_return_receivers = explode(",",$arr_recommend['return_receivers']);
				$arr_cancel = array();
				if(count($arr_receivers)>0){
					foreach($arr_return_receivers as $k=>$v){
						//查看当前是否存在
						if(in_array($v,$arr_receivers)){
							$cur_key = array_search($v,$arr_receivers);
							unset($arr_receivers[$cur_key]);
						}
						$sql = "SELECT * FROM db_product_develop WHERE sku = '{$rs['goods_parent_sku']}' AND  return_reason like '%{$v}-2%' ";
						$rr  = $this->db->query($sql)->row_array();
						if(count($rr)>0){
							$arr_cancel[] = $arr_return_receivers[$k]; //已经取消
							unset($arr_return_receivers[$k]);
						}
					}
				}

			    $str_cancel = implode(",",$arr_cancel);
				$str_return_receivers = implode(",",$arr_return_receivers);
				//$str_return_receivers = $arr_recommend['return_receivers'];
				$str_receivers = implode(",",$arr_receivers);
				$rs_receivers['name'] = "";
				$rs_return_receivers['name'] = "";
				$rs_cancel['name'] = "";
				if($arr_receivers){
					$sql_receivers = "SELECT GROUP_CONCAT(name) as name FROM d_user WHERE type = 1 AND  id in ({$str_receivers}) ";
					$rs_receivers  = $this->db->query($sql_receivers)->row_array();
				}
				if($str_return_receivers){
					$sql_return_receivers = "SELECT GROUP_CONCAT(name) as name FROM d_user WHERE type = 1 AND  id in ({$str_return_receivers}) ";
					$rs_return_receivers  = $this->db->query($sql_return_receivers)->row_array();
				}
				if($arr_cancel){
					$sql_cancel = "SELECT GROUP_CONCAT(name) as name FROM d_user WHERE type = 1 AND  id in ({$str_cancel}) ";
					$rs_cancel  = $this->db->query($sql_cancel)->row_array();
				}
				$dates[$key]["str_cancel"] = $rs_cancel['name']?$rs_cancel['name']:"";
				$dates[$key]["str_receivers"] = $rs_receivers['name']?$rs_receivers['name']:"";
				$dates[$key]["str_return_receivers"] = $rs_return_receivers['name']?$rs_return_receivers['name']:"";
			}else{
				$dates[$key]["str_cancel"] = "";
				$dates[$key]["str_receivers"] = "";
				$dates[$key]["str_return_receivers"] = "";
			}
			//直发销量
			$this->load->model('Product_m', 'product');
			$arr_salenum = $this->product->getallstock($rs["goods_parentID"]);

			$sql_goods = "SELECT GROUP_CONCAT(goods_id) as goods_id FROM db_goods WHERE goods_parent_sku = '{$rs['goods_parent_sku']}' ";
			$rs_goods  = $this->db->query($sql_goods)->row_array();

			//总待发货
			$sql = "SELECT sum(goods_number) as goods_number FROM db_order_sell A left join db_order_list B on A.`order_id` = B.order_id where A.`goods_id` in ({$rs_goods['goods_id']}) AND B.order_status in (2,3) AND  B.is_nodelivery = 0  and B.shipping_status = 5 ";
			$ress = $this->db->query($sql)->row_array();
			//销量
			$dates[$key]["saleNum"] = $arr_salenum['salenum']+$ress['goods_number'];



			 $goods_images = $this->db->get_where('db_goods_images', 'goods_id=' . $rs["goods_id"])->result_array();
             $thumb_url = $goods_images[0]["thumb_url"];
			 if($thumb_url=="")
			{
				 $dates[$key]["imagesurl"] = "/fuman/images/s-l500.jpg";
			}else{
			 $dates[$key]["imagesurl"] = $goods_images[0]['thumb_url'];
			}
			 $dates[$key]["g_updatetime"] = date("Y-m-d",strtotime($rs['g_updatetime']));
		}
		return $dates;
	}
	function getshop()
	{
		$sql = "SELECT * FROM  d_shop WHERE uploadID = '".$_COOKIE['userid']."'";
		$query = $this->db->query($sql);
        $dates = $query->result_array();
		if(count($dates)>0)
		{
			foreach($dates as $key=>$rs)
			{
				$shopname[] = $rs['name'];
			}
    		$whereshop = implode("','",$shopname);
		}
		return $whereshop;
	}
	function newstock($num=0)
	{
		$page = $num*20;
		$sql = "SELECT * FROM  db_goods_handstorage WHERE 1 AND istype =1 AND tostock =1 order by id desc LIMIT 20 OFFSET $page";
		$query = $this->db->query($sql);
        $dates = $query->result_array();
		foreach($dates as $key=>$rs)
		{
			 $storage = $this->db->get_where('db_goods_stock', " goods_id = '" . $rs["goods_id"] . "' and storage_id=1")->result_array();
			 $dates[$key]["stock"] = $storage[0]['stock'];
			 $dates[$key]["g_updatetime"] = date("Y-m-d",$rs['intime']);
		}
		return $dates;
	}
	function newrecommend($num=0)
	{
		$userid = $_COOKIE['userid'];
		$page = $num*7;
		$sql = "SELECT * FROM  db_external_buy WHERE 1 AND del_flag = 0 AND  ( return_receivers not like '%{$userid}%' or return_reason IS NULL) AND receivers like '%,".$userid.",%' order by return_reason asc LIMIT 7 OFFSET $page";
		$query = $this->db->query($sql);
		$dates = $query->result_array();
		foreach($dates as $key=>$value){
			$return_receiver = explode(",",$value['return_receivers']);
			$return_reason   = explode(",",$value['return_reason']);
			if(in_array($_COOKIE['userid'],$return_receiver)){
				$dates[$key]['cancel_flag'] = 0;
				$dates[$key]['cancel_reason'] = 0;
					foreach ($return_reason as $kk => $vv) {
						$arr_reason = explode("-", $vv);
						if ($_COOKIE['userid'] == $arr_reason[0]) {
							$dates[$key]['cancel_flag'] = $arr_reason[1];
							$str_con = $arr_reason[0] . "-" . $arr_reason[1] . "-";
							$dates[$key]['cancel_reason']  = str_replace($str_con, "", $vv);
							break;
						}
					}
			
				$dates[$key]["return_flag"] = 1;
			}else{
				$dates[$key]["return_flag"] = 0;
			}
			$dates[$key]["external_buy"] = 1;
		}
	
		//其他推荐
		$nkey=count($dates)+1;
		$sql = "SELECT * FROM  db_goods_recommend WHERE 1 AND is_stop = 0 AND  ( return_receivers not like '%{$userid}%' or return_reason IS NULL) AND receivers like '%,".$userid.",%' order by return_reason asc LIMIT 7 OFFSET $page";
		$query = $this->db->query($sql);
		$datesdb = $query->result_array();
		foreach($datesdb as $key=>$value){
			
			$dates[$nkey]["p_parent_name"] = $value['goods_name'];
			$dates[$nkey]["sku"] = $value['parent_sku'];
			$dates[$nkey]["id"] = $value['id'];
			$dates[$nkey]["protector"] = $value['recommend'];
			$dates[$nkey]["linkUrl"] = $value['linkUrl'];
			

			$return_receiver = explode(",",$value['return_receivers']);
			$return_reason   = explode(",",$value['return_reason']);
			if(in_array($_COOKIE['userid'],$return_receiver)){
				$dates[$nkey]['cancel_flag'] = 0;
				$dates[$nkey]['cancel_reason'] = 0;
					foreach ($return_reason as $kk => $vv) {
						$arr_reason = explode("-", $vv);
						if ($_COOKIE['userid'] == $arr_reason[0]) {
							$dates[$nkey]['cancel_flag'] = $arr_reason[1];
							$str_con = $arr_reason[0] . "-" . $arr_reason[1] . "-";
							$dates[$nkey]['cancel_reason']  = str_replace($str_con, "", $vv);
							break;
						}
					}
			
				$dates[$nkey]["return_flag"] = 1;
			}else{
				$dates[$nkey]["return_flag"] = 0;
			}
			$dates[$nkey]["external_buy"] = 0;

			$nkey = $nkey+1;
		}

		//新品开发
		$newkey=count($dates)+1;
		$page = $num*7;
		$sql = "SELECT * FROM  db_product_develop WHERE 1 AND  ( return_receivers not like '%{$userid}%' or return_reason IS NULL) AND receivers like '%,".$userid.",%' order by return_reason asc LIMIT 7 OFFSET $page";
		$query = $this->db->query($sql);
		$datesnew = $query->result_array();
		foreach($datesnew as $key=>$value){
			$dates[$newkey]["p_parent_name"] = $value['name'];
			$dates[$newkey]["sku"] = $value['sku'];
			$dates[$newkey]["id"] = $value['id'];
			$dates[$newkey]["protector"] = $value['createName'];
			$dates[$newkey]["linkUrl"] = $value['linkUrl'];

			$return_receiver = explode(",",$value['return_receivers']);
			$return_reason   = explode(",",$value['return_reason']);
			if(in_array($_COOKIE['userid'],$return_receiver)){
				$dates[$newkey]['cancel_flag'] = 0;
				$dates[$newkey]['cancel_reason'] = 0;
				foreach ($return_reason as $kk => $vv) {
					$arr_reason = explode("-", $vv);
					if ($_COOKIE['userid'] == $arr_reason[0]) {
						$dates[$newkey]['cancel_flag'] = $arr_reason[1];
						$str_con = $arr_reason[0] . "-" . $arr_reason[1] . "-";
						$dates[$newkey]['cancel_reason']  = str_replace($str_con, "", $vv);
						break;
					}
				}
				$dates[$newkey]["return_flag"] = 1;
			}else{
				$dates[$newkey]["return_flag"] = 0;
			}
			$dates[$newkey]["external_buy"] = 2;
			$newkey = $newkey+1;
		}

		//print_r($dates);
	
		return $dates;
	}
	//工厂扫件
	function newfactoryscan(){
		$where="";
		if($_COOKIE["classid"] ==12)	//工厂1人员
		{
			$where.=" AND factoryId = 1";
		}

		if($_COOKIE["classid"] ==13)	//工厂2人员
		{
			$where.=" AND factoryId = 2";
		}
		if($_COOKIE["classid"] ==16)	//外部开发
		{
			$where.=" AND factoryId = 7";
		}
		$where .= " AND type = 2 "; // 批量订单
		$sql = "SELECT * FROM  db_factory_scanorder WHERE 1 ".$where." order by id desc LIMIT 10 OFFSET 0";
		$query = $this->db->query($sql);
		$dates = $query->result_array();
		return $dates;
	}
	//一定时间内无订单的店铺
	function shopnoorder(){
		$shopdays = trim($this->config->item("shopdays"));
		$sql = "SELECT * FROM d_shop WHERE del_flag = 0 ";
		$rs  = $this->db->query($sql)->result_array();
		$cur_time = time()-$shopdays*86400;
		$cur_times = date("Y-m-d",$cur_time);
		$arr_shop = array();
		if(count($rs)>0){
			foreach($rs as $k=>$v){
				//判断店铺下过订单 并且近期没有订单导入系统
				$sql_order = "SELECT shopid,shopname,dates FROM db_order_list WHERE shopid = '{$v['id']}' ORDER BY order_id DESC limit 1 ";
				$rs_order  = $this->db->query($sql_order)->row_array();
				if(count($rs_order)>0){
					$order_time = strtotime($rs_order['dates']);
					if($cur_times== $rs_order['dates']){
						$arr_shop[$k]['shopid'] = $rs_order['shopid'];
						$arr_shop[$k]['shopname'] = $rs_order['shopname'];
						$arr_shop[$k]['shopdays'] = $shopdays;
					}
				}
			}
		}
		return $arr_shop;
	}
	//一定时间内无订单的店铺
	function shopnofbaorder(){
		$shopdays = trim($this->config->item("fbashopdays"));
		$sql = "SELECT * FROM d_shop WHERE del_flag = 0  AND platfrom = 2  ";
		$rs  = $this->db->query($sql)->result_array();
		$cur_time = time()-$shopdays*86400;
		$cur_times = date("Y-m-d",$cur_time);
		$arr_shop = array();
		if(count($rs)>0){
			foreach($rs as $k=>$v){
				//判断店铺下过订单 并且近期没有订单导入系统
				$sql_order = "SELECT shopid,shopname,dates FROM db_amzordertmp WHERE shopid = '{$v['id']}' ORDER BY id DESC limit 1 ";
				$rs_order  = $this->db->query($sql_order)->row_array();
				if(count($rs_order)>0){
					$order_time = strtotime($rs_order['dates']);
					if($cur_times == $rs_order['dates']){
						$arr_shop[$k]['shopid'] = $rs_order['shopid'];
						$arr_shop[$k]['shopname'] = $rs_order['shopname'];
						$arr_shop[$k]['shopdays'] = $shopdays;
					}
				}
			}
		}
		return $arr_shop;
	}
	
	function diffBetweenTwoDays ($day1, $day2){  
		$second1 = strtotime($day1); 
		$second2 = strtotime($day2);     
		if ($second1 < $second2) 
		{    
			$tmp = $second2;    
			$second2 = $second1;   
			$second1 = $tmp; 
		} 
		return ($second1 - $second2) / 86400;
	}
	
}
